package nckd.wnqjt.tmc.tda.plugin.form;

import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

import kd.bos.algo.DataSet;
import kd.bos.entity.report.AbstractReportListDataPlugin;
import kd.bos.entity.report.ReportQueryParam;
import kd.bos.orm.query.QCP;
import kd.bos.orm.query.QFilter;
import kd.bos.servicehelper.QueryServiceHelper;

/**
 * @author husheng
 * @date 2024-11-04 20:08
 * @description
 */
public class ReceivablebillTermListDataPlugin extends AbstractReportListDataPlugin {
    @Override
    public DataSet query(ReportQueryParam reportQueryParam, Object o) {
        Date currentDate = this.getCurrentDate();
        // 收票登记
        String receivablebillSql = "id,company nckd_company,company.name companyName,company.number companyNumber,bizdate,draftbilltype.number draftbilltypeNumber,amount,draftbillstatus,draftbillexpiredate";
        QFilter filter = new QFilter("rptype", QCP.equals, "receivebill")
                .and("draftbilltype.number", QCP.in, new String[]{"102", "104", "CDMT-79A2C33B"})
                .and("billstatus", QCP.equals, "C")
                .and("bizdate", QCP.less_equals, currentDate)
                .and("draftbillexpiredate", QCP.large_equals, currentDate)
                .and("draftbillstatus", QCP.in, new String[]{"registered", "endorsed", "pledged", "collocated", "discounted", "collected"})
                .and("draftbilltranstatus", QCP.equals, "success");
        DataSet cdmReceivablebill = QueryServiceHelper.queryDataSet(this.getClass().getName(), "cdm_receivablebill", receivablebillSql, filter.toArray(), null);

        // 根据票据状态分开取值
        DataSet cdmReceivablebill1 = cdmReceivablebill.copy().filter("draftbillstatus = 'registered'");
        DataSet cdmReceivablebill2 = cdmReceivablebill.copy().filter("draftbillstatus in ('endorsed','pledged','discounted','collected')");
        DataSet cdmReceivablebill3 = cdmReceivablebill.copy().filter("draftbillstatus = 'collocated'");

        // 业务处理
        String drafttradebillSql = "id,entrys.draftbill,bizdate drbizdate";

        /*
            已登记的票据有两种，一种是未做任何业务处理的，另一种是做过质押解除
            如果登记状态的票据有解除质押的，需保留解除质押的，业务日期小于等于查询日期
         */
        QFilter filter1 = new QFilter("tradetype", QCP.equals, "rlspledge");
        DataSet cdmDrafttradebill1 = QueryServiceHelper.queryDataSet(this.getClass().getName(), "cdm_drafttradebill", drafttradebillSql, filter1.toArray(), null);

        DataSet finish1 = cdmReceivablebill1.leftJoin(cdmDrafttradebill1).on("id", "entrys.draftbill").select(cdmReceivablebill2.getRowMeta().getFieldNames(), new String[]{"drbizdate"}).finish();

        DataSet drafttradebill1 = finish1.copy().filter("drbizdate is null");
        DataSet drafttradebill2 = finish1.copy().filter("drbizdate is not null and drbizdate <= to_date('" + currentDate + "','yyyy-MM-dd hh:mm:ss')");
        DataSet finish2 = drafttradebill1.select(cdmReceivablebill.getRowMeta().getFieldNames());
        DataSet finish3 = drafttradebill2.select(cdmReceivablebill.getRowMeta().getFieldNames());


        // 业务日期大于查询日期
        QFilter filter2 = new QFilter("bizdate", QCP.large_than, currentDate)
                .and("tradetype", QCP.in, new String[]{"endorse", "pledge", "discount", "collect"});
        DataSet cdmDrafttradebill2 = QueryServiceHelper.queryDataSet(this.getClass().getName(), "cdm_drafttradebill", drafttradebillSql, filter2.toArray(), null);

        DataSet finish4 = cdmReceivablebill2.join(cdmDrafttradebill2).on("id", "entrys.draftbill").select(cdmReceivablebill2.getRowMeta().getFieldNames()).finish();

        // 合并结果集
        DataSet receivablebill = cdmReceivablebill3.union(finish2, finish3, finish4);

        // 获取所有组织id
        DataSet companyDataSet = receivablebill.copy().groupBy(new String[]{"nckd_company"}).finish();

        // 根据 票据到期日-查询日 分类
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        // 银行承兑
        DataSet receivablebill1 = receivablebill.copy().filter("draftbilltypeNumber = '102' and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') <= 30")
                .groupBy(new String[]{"nckd_company"}).count().sum("amount").finish();
        DataSet receivablebill2 = receivablebill.copy().filter("draftbilltypeNumber = '102' and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') <= 60 and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') > 30")
                .groupBy(new String[]{"nckd_company"}).count().sum("amount").finish();
        DataSet receivablebill3 = receivablebill.copy().filter("draftbilltypeNumber = '102' and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') <= 90 and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') > 60")
                .groupBy(new String[]{"nckd_company"}).count().sum("amount").finish();
        DataSet receivablebill4 = receivablebill.copy().filter("draftbilltypeNumber = '102' and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') <= 120 and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') > 90")
                .groupBy(new String[]{"nckd_company"}).count().sum("amount").finish();
        DataSet receivablebill5 = receivablebill.copy().filter("draftbilltypeNumber = '102' and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') <= 150 and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') > 120")
                .groupBy(new String[]{"nckd_company"}).count().sum("amount").finish();
        DataSet receivablebill6 = receivablebill.copy().filter("draftbilltypeNumber = '102' and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') <= 180 and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') > 150")
                .groupBy(new String[]{"nckd_company"}).count().sum("amount").finish();
        // 商业承兑
        DataSet receivablebill7 = receivablebill.copy().filter("draftbilltypeNumber = '104' and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') <= 30")
                .groupBy(new String[]{"nckd_company"}).count().sum("amount").finish();
        DataSet receivablebill8 = receivablebill.copy().filter("draftbilltypeNumber = '104' and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') <= 60 and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') > 30")
                .groupBy(new String[]{"nckd_company"}).count().sum("amount").finish();
        DataSet receivablebill9 = receivablebill.copy().filter("draftbilltypeNumber = '104' and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') <= 90 and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') > 60")
                .groupBy(new String[]{"nckd_company"}).count().sum("amount").finish();
        DataSet receivablebill10 = receivablebill.copy().filter("draftbilltypeNumber = '104' and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') <= 120 and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') > 90")
                .groupBy(new String[]{"nckd_company"}).count().sum("amount").finish();
        DataSet receivablebill11 = receivablebill.copy().filter("draftbilltypeNumber = '104' and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') <= 150 and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') > 120")
                .groupBy(new String[]{"nckd_company"}).count().sum("amount").finish();
        DataSet receivablebill12 = receivablebill.copy().filter("draftbilltypeNumber = '104' and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') <= 180 and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') > 150")
                .groupBy(new String[]{"nckd_company"}).count().sum("amount").finish();
        // 类票据
        DataSet receivablebill13 = receivablebill.copy().filter("draftbilltypeNumber = 'CDMT-79A2C33B' and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') <= 30")
                .groupBy(new String[]{"nckd_company"}).count().sum("amount").finish();
        DataSet receivablebill14 = receivablebill.copy().filter("draftbilltypeNumber = 'CDMT-79A2C33B' and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') <= 60 and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') > 30")
                .groupBy(new String[]{"nckd_company"}).count().sum("amount").finish();
        DataSet receivablebill15 = receivablebill.copy().filter("draftbilltypeNumber = 'CDMT-79A2C33B' and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') <= 90 and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') > 60")
                .groupBy(new String[]{"nckd_company"}).count().sum("amount").finish();
        DataSet receivablebill16 = receivablebill.copy().filter("draftbilltypeNumber = 'CDMT-79A2C33B' and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') <= 120 and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') > 90")
                .groupBy(new String[]{"nckd_company"}).count().sum("amount").finish();
        DataSet receivablebill17 = receivablebill.copy().filter("draftbilltypeNumber = 'CDMT-79A2C33B' and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') <= 150 and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') > 120")
                .groupBy(new String[]{"nckd_company"}).count().sum("amount").finish();
        DataSet receivablebill18 = receivablebill.copy().filter("draftbilltypeNumber = 'CDMT-79A2C33B' and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') <= 180 and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') > 150")
                .groupBy(new String[]{"nckd_company"}).count().sum("amount").finish();


        DataSet dataSet = companyDataSet.leftJoin(receivablebill1).on("nckd_company", "nckd_company").select(new String[]{"nckd_company"}, new String[]{"count as nckd_strokecount1", "amount as nckd_amount1"}).finish();
        dataSet = dataSet.leftJoin(receivablebill2).on("nckd_company", "nckd_company").select(dataSet.getRowMeta().getFieldNames(), new String[]{"count as nckd_strokecount2", "amount as nckd_amount2"}).finish();
        dataSet = dataSet.leftJoin(receivablebill3).on("nckd_company", "nckd_company").select(dataSet.getRowMeta().getFieldNames(), new String[]{"count as nckd_strokecount3", "amount as nckd_amount3"}).finish();
        dataSet = dataSet.leftJoin(receivablebill4).on("nckd_company", "nckd_company").select(dataSet.getRowMeta().getFieldNames(), new String[]{"count as nckd_strokecount4", "amount as nckd_amount4"}).finish();
        dataSet = dataSet.leftJoin(receivablebill5).on("nckd_company", "nckd_company").select(dataSet.getRowMeta().getFieldNames(), new String[]{"count as nckd_strokecount5", "amount as nckd_amount5"}).finish();
        dataSet = dataSet.leftJoin(receivablebill6).on("nckd_company", "nckd_company").select(dataSet.getRowMeta().getFieldNames(), new String[]{"count as nckd_strokecount6", "amount as nckd_amount6"}).finish();
        dataSet = dataSet.leftJoin(receivablebill7).on("nckd_company", "nckd_company").select(dataSet.getRowMeta().getFieldNames(), new String[]{"count as nckd_strokecount7", "amount as nckd_amount7"}).finish();
        dataSet = dataSet.leftJoin(receivablebill8).on("nckd_company", "nckd_company").select(dataSet.getRowMeta().getFieldNames(), new String[]{"count as nckd_strokecount8", "amount as nckd_amount8"}).finish();
        dataSet = dataSet.leftJoin(receivablebill9).on("nckd_company", "nckd_company").select(dataSet.getRowMeta().getFieldNames(), new String[]{"count as nckd_strokecount9", "amount as nckd_amount9"}).finish();
        dataSet = dataSet.leftJoin(receivablebill10).on("nckd_company", "nckd_company").select(dataSet.getRowMeta().getFieldNames(), new String[]{"count as nckd_strokecount10", "amount as nckd_amount10"}).finish();
        dataSet = dataSet.leftJoin(receivablebill11).on("nckd_company", "nckd_company").select(dataSet.getRowMeta().getFieldNames(), new String[]{"count as nckd_strokecount11", "amount as nckd_amount11"}).finish();
        dataSet = dataSet.leftJoin(receivablebill12).on("nckd_company", "nckd_company").select(dataSet.getRowMeta().getFieldNames(), new String[]{"count as nckd_strokecount12", "amount as nckd_amount12"}).finish();
        dataSet = dataSet.leftJoin(receivablebill13).on("nckd_company", "nckd_company").select(dataSet.getRowMeta().getFieldNames(), new String[]{"count as nckd_strokecount13", "amount as nckd_amount13"}).finish();
        dataSet = dataSet.leftJoin(receivablebill14).on("nckd_company", "nckd_company").select(dataSet.getRowMeta().getFieldNames(), new String[]{"count as nckd_strokecount14", "amount as nckd_amount14"}).finish();
        dataSet = dataSet.leftJoin(receivablebill15).on("nckd_company", "nckd_company").select(dataSet.getRowMeta().getFieldNames(), new String[]{"count as nckd_strokecount15", "amount as nckd_amount15"}).finish();
        dataSet = dataSet.leftJoin(receivablebill16).on("nckd_company", "nckd_company").select(dataSet.getRowMeta().getFieldNames(), new String[]{"count as nckd_strokecount16", "amount as nckd_amount16"}).finish();
        dataSet = dataSet.leftJoin(receivablebill17).on("nckd_company", "nckd_company").select(dataSet.getRowMeta().getFieldNames(), new String[]{"count as nckd_strokecount17", "amount as nckd_amount17"}).finish();
        dataSet = dataSet.leftJoin(receivablebill18).on("nckd_company", "nckd_company").select(dataSet.getRowMeta().getFieldNames(), new String[]{"count as nckd_strokecount18", "amount as nckd_amount18"}).finish();

        dataSet = dataSet.addFields(new String[]{"nckd_amount1 + nckd_amount7 + nckd_amount13", "nckd_strokecount1 + nckd_strokecount7 + nckd_strokecount13",
                        "nckd_amount2 + nckd_amount8 + nckd_amount14", "nckd_strokecount2 + nckd_strokecount8 + nckd_strokecount14",
                        "nckd_amount3 + nckd_amount9 + nckd_amount15", "nckd_strokecount3 + nckd_strokecount9 + nckd_strokecount15",
                        "nckd_amount4 + nckd_amount10 + nckd_amount16", "nckd_strokecount4 + nckd_strokecount10 + nckd_strokecount16",
                        "nckd_amount5 + nckd_amount11 + nckd_amount17", "nckd_strokecount5 + nckd_strokecount11 + nckd_strokecount17",
                        "nckd_amount6 + nckd_amount12 + nckd_amount18", "nckd_strokecount6 + nckd_strokecount12 + nckd_strokecount18"},
                new String[]{"nckd_amountsum1", "nckd_strokecountsum1", "nckd_amountsum2", "nckd_strokecountsum2",
                        "nckd_amountsum3", "nckd_strokecountsum3", "nckd_amountsum4", "nckd_strokecountsum4",
                        "nckd_amountsum5", "nckd_strokecountsum5", "nckd_amountsum6", "nckd_strokecountsum6"});

        return dataSet;
    }

    private Date getCurrentDate() {
        Calendar cal = new GregorianCalendar();
//        cal.set(11, 0);
//        cal.set(12, 0);
//        cal.set(13, 0);
//        cal.set(14, 0);
        return cal.getTime();
    }
}
